package org.fage.semples.util;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NotOLE2FileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.fage.semples.model.ExamUserVO;

public class ExcelUtils {
	public static final String OFFICE_EXCEL_2003_POSTFIX = ".xls";
	public static final String OFFICE_EXCEL_2007_POSTFIX = ".xlsx";
	
	public static Workbook openWorkbook(InputStream is, String postfix){
		Workbook hssfWorkbook = null;
		try{
			if(OFFICE_EXCEL_2003_POSTFIX.equals(postfix)){
				hssfWorkbook = new HSSFWorkbook(is);
			}else if(OFFICE_EXCEL_2007_POSTFIX.equals(postfix)){
				hssfWorkbook = new XSSFWorkbook(is);
			}
		}catch(Exception e){
			if(e instanceof NotOLE2FileException){
				return null;
			}
			e.printStackTrace();
		}
		return hssfWorkbook;
	}
	
	public static List<ExamUserVO> getStudentListByExcel(Workbook wk){
		final String GENDER_MALE_STRING ="男", GENDER_FEMALE_STRING ="女";
		final Integer GENDER_MALE_INTEGER =1, GENDER_FEMALE_INTEGER =0;
		List<ExamUserVO> ExamUserVOList = new ArrayList<ExamUserVO>();
		
		Sheet sheet = wk.getSheetAt(0);
		int maxCellNumber = 0;
		int maxRowNumber = sheet.getLastRowNum();
		ExamUserVO examUserVO = null;
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		for(int currentRowNumber = 1; currentRowNumber <= maxRowNumber; currentRowNumber++ ){
			Row row = sheet.getRow(currentRowNumber);
			maxCellNumber = row.getLastCellNum();
			examUserVO = new ExamUserVO();
			for(int currentCellNumber = 0; currentCellNumber < maxCellNumber; currentCellNumber++){
				Cell cell = row.getCell(currentCellNumber);
				
				switch(currentCellNumber){
					case 0:
						examUserVO.setUserId((String)getCellValue(cell));
						break;
					case 1:
						examUserVO.setUserName((String)getCellValue(cell));
						break;
					case 2:
						String decodingPassWord = getCellValue(cell);
						examUserVO.setPassword(MD5Utils.GetMd5Code(decodingPassWord));
						break;
					case 3:
						examUserVO.setTel((String)getCellValue(cell));
						break;
					case 4:
						examUserVO.setEmail((String)getCellValue(cell));
						break;
					case 5:
						examUserVO.setAddress((String)getCellValue(cell));
						break;
					case 6:
						examUserVO.setBirthday((String)sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())));
						break;
					case 7:
						if(GENDER_MALE_STRING.equals(getCellValue(cell))){
							examUserVO.setGender(GENDER_MALE_INTEGER);
						}else if(GENDER_FEMALE_STRING.equals(getCellValue(cell)))
							examUserVO.setGender(GENDER_FEMALE_INTEGER);
						break;
					case 8:
						examUserVO.setLastLockTime(new Date());
						break;
					case 9:
						examUserVO.setLoginFailCount(0);
						break;
				}
			}
			ExamUserVOList.add(examUserVO);
		}
		
		return ExamUserVOList;
	}
	
	@SuppressWarnings("static-access")
	private static String getCellValue(Cell cell){
		if(cell == null){
			return "";
		}
		if(cell.getCellType() == cell.CELL_TYPE_BOOLEAN){
			return String.valueOf(cell.getBooleanCellValue());
		}else if(cell.getCellType() == cell.CELL_TYPE_NUMERIC){
			DecimalFormat df = new DecimalFormat("0");
			return df.format(cell.getNumericCellValue());
		}else{
			return String.valueOf(cell.getStringCellValue());
		}
	}
}
